<?php

namespace App\Http\Controllers\Pay;

use App\Http\Controllers\Controller;
use App\Models\Pay\PayImportData;
use App\Models\Pay\PayReport;
use App\Models\Pay\PayReportFields;
use Illuminate\Http\Request;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use function GuzzleHttp\headers_from_lines;

class LeaderPayController extends Controller
{
    /**
     * 领导工资列表
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function leaderPayList(Request $request)
    {
        return response()->formatJson($this->getLeaderPay($request->all()));
    }

    /**
     * 获取领导工资数据
     *
     * @param $params
     *
     * @return mixed
     */
    public function getLeaderPay($params)
    {
        $leader = ['张晓强', '熊卫军', '钱万辉', '王彩东', '兰晖'];

        // 获取所有数据
        $data = PayImportData::query()
            ->whereIn('name', $leader)
            ->where('month', $params['date'].'-01')
            ->get()
            ->groupBy('name')
            ->toArray();

        // 获取数据对应关系
        $reportId = PayReport::query()->where('report_name', '企业负责人工资表')->first()->id;
        $fields = [];
        $parent = PayReportFields::query()
            ->where('report_id', $reportId)
            ->where('parent_id', 0)
            ->orderBy('sort')
            ->get()
            ->toArray();

        foreach ($parent as $v) {
            $son = PayReportFields::query()
                ->where('report_id', $reportId)
                ->where('parent_id', $v['id'])
                ->orderBy('sort')
                ->get()
                ->toArray();

            if (! $son) {
                $fields[] = $v;
            }
            foreach ($son as $key => $value) {
                $value['parent'] = $v['header_name'];
                $fields[] = $value;
            }
        }
        // 匹配数据
        $result = [];
        $ignoreKey = ['姓名', '出勤天数', '线路', '备注', '类别'];
        foreach ($data as $name => $v) {
            $part = [];
            foreach ($fields as $key => $item) {
                if ($item['header_name'] === '姓名') {
                    $part[$item['header_name']] = $name;
                } else {
                    if ($item['table_name']) {
                        $sign = collect($v)->where('table_name', $item['table_name'])->first();
                        if ($sign) {
                            $signData = $sign['data'];
                            $signDataArr = json_decode($signData, true);
                            if (in_array($item['column_name'], $ignoreKey, true)) {
                                $part[$item['header_name']] = array_key_exists($item['column_name'], $signDataArr) ?
                                    $signDataArr[$item['column_name']] :
                                    '';
                            } else {
                                $part[$item['header_name']] = array_key_exists($item['column_name'], $signDataArr) ?
                                    number_format($signDataArr[$item['column_name']], 2, '.', '') :
                                    0;
                            }
                        } else {
                            $part[$item['header_name']] = '';
                        }
                    } else {
                        $part[$item['header_name']] = '';
                    }
                }
            }
            $result[] = $part;
        }

        // 计算公式

        $countFields = PayReportFields::query()
            ->where('type', '合计')->where('report_id', $reportId)->orderBy('sort')->get()->toArray();
        foreach ($result as $kk => $vv) {
            foreach ($countFields as $v) {
                $formula = explode('+', $v['formula']);
                if ($v['calculation'] === '+') {
                    $count = 0;
                    foreach ($formula as $value) {
                        $a = array_key_exists($value, $vv) && $vv[$value] !== '' ? $vv[$value] : 0;
                        $count += $a;
                    }
                    $result[$kk][$v['header_name']] = number_format($count, 2, '.', '');
                }
            }
        }
        // foreach ($result as $kk => $vv) {
        //     $result[$kk]['实发工资'] = number_format($vv['应发合计'] - $vv['应扣合计'], 2, '.', '');
        // }

        // 判断列是否有值，没有值则不显示
        $columns = (new AdminPayController())->getColumns('企业负责人工资表');

        foreach ($columns as $k => $column) {
            if (array_key_exists('is_show', $column) && $column['is_show'] == 2) {
                $colData = array_column($result, $column['header_name']);
                $isEmpty = true;
                foreach ($colData as $value) {
                    if (! empty($value)) {
                        $isEmpty = false;
                        break;
                    }
                }
                if (! $isEmpty) {
                    unset($columns[$k]);
                }
            }
            if (array_key_exists('children', $column)) {
                foreach ($column['children'] as $kk => $item) {
                    if (array_key_exists('is_show', $item) && $item['is_show'] == 2) {
                        $colData = array_column($result, $item['header_name']);
                        $isEmpty = true;
                        foreach ($colData as $value) {
                            if ((float) $value != 0) {
                                $isEmpty = false;
                                break;
                            }
                        }
                        if ($isEmpty) {
                            unset($columns[$k]['children'][$kk]);
                        }
                    }
                }
            }
        }
        // 重置键值
        $columns = $this->rebuildKeys($columns);

        return ['personnelData' => $result, 'columns' => $columns];
    }

    public function rebuildKeys($array)
    {
        if (! is_array($array)) {
            return $array;
        }
        $keys = implode('', array_keys($array));
        if (is_numeric($keys)) {
            $array = array_values($array);
        }
        $array = array_map([$this, 'rebuildKeys'], $array);

        return $array;
    }

    /**
     * 导出报表
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportLeaderPay(Request $request)
    {
        $params = $request->input();
        $tableData = $this->getLeaderPay($params);
        $data = $tableData['personnelData'];
        $header = $tableData['columns'];

        $columnName = range('A', 'Z');
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');
        // 添加表头
        $spreadsheet->setActiveSheetIndex(0)->setCellValue('A1', '领导工资表');
        // 输出表头
        // 移除无用列
        foreach ($header as $k => $v) {
            if (isset($v['type']) && $v['type'] === 'index') {
                unset($header[$k]);
            }
        }
        $header = $this->rebuildKeys($header);
        $headerColumn = [];
        $dataColumn = [];
        $index = 0;
        foreach ($header as $k => $v) {
            $headerColumn[2][$index] = $v['title'];
            if (array_key_exists('children', $v)) {
                foreach ($v['children'] as $kk => $vv) {
                    $headerColumn[3][$index] = $vv['title'];
                    $dataColumn[] = $vv['title'];
                    $index++;
                }
            } else {
                $dataColumn[] = $v['title'];
                $index++;
            }
        }
        $countColumn = count($dataColumn);
        $tableData = [];
        foreach ($dataColumn as $k => $column) {
            foreach ($data as $kk => $vv) {
                if (array_key_exists($column, $vv)) {
                    $tableData[$kk][] = $vv[$column];
                }
            }
        }

        // 填充表头
        foreach ($headerColumn as $k => $v) {
            $keys = array_keys($v);
            if ($k === 2) {
                $lastKey = 0;
                foreach ($keys as $key) {
                    if ($key !== $lastKey && ($key - $lastKey) > 1) {
                        $spreadsheet->getActiveSheet()->mergeCells($columnName[$lastKey].$k.':'.$columnName[$key - 1].$k);
                    }
                    $lastKey = $key;
                }
            }
            foreach ($v as $kk => $vv) {
                $column = $columnName[$kk].$k;
                $spreadsheet->setActiveSheetIndex(0)->setCellValue($column, $vv);
            }
        }
        foreach ($headerColumn[2] as $k => $v) {
            if (! array_key_exists($k, $headerColumn[3])) {
                $spreadsheet->getActiveSheet()->mergeCells($columnName[$k].'2:'.$columnName[$k].'3');
            }
        }
        $spreadsheet->getActiveSheet()->mergeCells('A1:'.$columnName[$countColumn - 1].'1');
        // 添加动态数据
        $spreadsheet->getActiveSheet()
            ->fromArray(
                $tableData,      // The data to set
                null,       // Array values with this value will not be set
                'A4'        // Top left coordinate of the worksheet range where
            // we want to set these values (default is A1)
            );
        //  设置宽度
        foreach (array_keys($dataColumn) as $k => $v) {
            $column = $columnName[$k];
            $spreadsheet->getActiveSheet()->getColumnDimension($column)->setWidth(13);
        }
        // 设置高度
        $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
        // 设置对齐方式
        $numberStyleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        $spreadsheet->getActiveSheet()->getStyle('D5:N8')->applyFromArray($numberStyleArray);
        $centerStyleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        $spreadsheet->getActiveSheet()->getStyle('A1:'.$columnName[$countColumn - 1].'3')->applyFromArray($centerStyleArray);
        $spreadsheet->getActiveSheet()->getStyle('A4:A'.(4 + count($tableData)))->applyFromArray($centerStyleArray);
        // 设置边框
        $allBordersStyleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                ],
            ],
        ];
        $spreadsheet->getActiveSheet()->getStyle('A1:'.$columnName[$countColumn - 1].'1')->applyFromArray($allBordersStyleArray);
        $spreadsheet->getActiveSheet()->getStyle('A2:'.$columnName[$countColumn - 1].(4 + count($tableData) - 1))->applyFromArray($allBordersStyleArray);
        // 设置千分位
        $spreadsheet->getActiveSheet()->getStyle('C4:'.$columnName[$countColumn - 1].(4 + count($tableData) - 1))->getNumberFormat()->setFormatCode('#,##0.00');
        // 重命名 worksheet
        $spreadsheet->getActiveSheet()->setTitle('sheet');
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        $spreadsheet->setActiveSheetIndex(0);
        // 设置字体大小
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);

        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="领导工资统计表_'.$params['date'].'.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');

        exit;
    }

    /**
     * 组织导出数据
     *
     * @param $payData
     *
     * @return array
     */
    public function buildData($payData)
    {
        $fields = [
            'index',
            'name',
            'attendance_days',
            'base_pay',
            'year_performance_pay',
            'car_allowance',
            'cool_fee',
            'should_pay',
            'endowment_insurance',
            'medical_insurance',
            'accumulation_fund',
            'individual_income_tax',
            'tu_membership_dues',
            'actual_pay',
        ];

        $data = [];
        // 整理数据，填充合并的单元格数据
        foreach ($payData as $k => $row) {
            $item = [];
            foreach ($fields as $field) {
                if ($field === 'index') {
                    $item['index'] = $k + 1;
                } else {
                    $item[$field] = $row[$field];
                }
            }

            $data[] = $item;
        }

        return $data;
    }
}
